/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;
import DTO.KHACHHANGDTO;
import SQL.DataProvider;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 *
 * @author NGUYEN
 */
public class KHACHHANGDAO {
    
    
    public ArrayList<KHACHHANGDTO> timkhachhangcoten(String ten)
    {
        DataProvider pro = new DataProvider();
        ArrayList<KHACHHANGDTO> ds = new ArrayList<KHACHHANGDTO>();
        ResultSet rs=null;
        try
        {
            pro.open();
            
            String query = "SELECT * FROM KHACHHANG WHERE TenKH LIKE '%"+ ten + "%'";
            rs = pro.excutequery(query);  
            while(rs.next())
            {
                KHACHHANGDTO dto = new KHACHHANGDTO();
                dto.setMakh(Integer.parseInt(rs.getString("MaKH")));
                dto.setTenkh(rs.getString("TenKH"));
                dto.setDiachi(rs.getString("DiaChi"));
                dto.setPhone(rs.getString("Phone"));
                ds.add(dto);
            }
            return ds;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }   
        finally
        {
            pro.close();
        }
        return ds;
    }
    
    public KHACHHANGDTO timkhachhangcoma(int ma)
    {
        DataProvider pro = new DataProvider();
        KHACHHANGDTO dto = new KHACHHANGDTO();
        ResultSet rs=null;
        try
        {
            pro.open();
            String query = "SELECT * FROM KHACHHANG WHERE MaKH = " + ma;
            rs = pro.excutequery(query);  
            if(rs.next())
            {
                dto.setMakh(Integer.parseInt(rs.getString("MaKH")));
                dto.setTenkh(rs.getString("TenKH"));
                dto.setDiachi(rs.getString("DiaChi"));
                dto.setPhone(rs.getString("Phone"));
            }
            return dto;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }   
        finally
        {
            pro.close();
        }
        return null;
    }
    
    public ArrayList<KHACHHANGDTO> laydanhsachkhachhang()
    {
        DataProvider pro = new DataProvider();
        ArrayList<KHACHHANGDTO> ds = new ArrayList<KHACHHANGDTO>();
        ResultSet rs=null;
        try
        {
            pro.open();
            
            String query = "SELECT * FROM KHACHHANG";
            rs = pro.excutequery(query);  
            while(rs.next())
            {
                KHACHHANGDTO dto = new KHACHHANGDTO();
                dto.setMakh(Integer.parseInt(rs.getString("MaKH")));
                dto.setTenkh(rs.getString("TenKH"));
                dto.setDiachi(rs.getString("DiaChi"));
                dto.setPhone(rs.getString("Phone"));
                ds.add(dto);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }   
        finally
        {
            pro.close();
        }
        return ds;
    }
    
    public ArrayList<KHACHHANGDTO> list_khachhang()
    {
        DataProvider pro = new DataProvider();
        ArrayList<KHACHHANGDTO> ds = new ArrayList<KHACHHANGDTO>();
        ResultSet rs=null;
        try
        {
            pro.open();
            String query = "SELECT * FROM KHACHHANG";
            rs = pro.excutequery(query);
            while(rs.next())
            {
                KHACHHANGDTO dto = new KHACHHANGDTO();
                dto.setMakh(Integer.parseInt(rs.getString("MaKH")));
                dto.setTenkh(rs.getString("TenKH"));
                ds.add(dto);
            }
        }
        catch(SQLException | NumberFormatException e)
        {
            e.printStackTrace();
        }
        finally
        {
            pro.close();
        }
        return ds;
    }
    
    public boolean themkhachhang(KHACHHANGDTO dto)
    {
        DataProvider pro = new DataProvider();
        int kq;
        try
        {
            pro.open();
            String query = "INSERT INTO KhachHang(TenKH,DiaChi,Phone) VALUES('"
                    +dto.getTenkh()+"',N'"
                    +dto.getDiachi()+"','"
                    +dto.getPhone()+"')";
            kq = pro.excuteupdate(query);
            if(kq>=0)
                return true;
        }
        catch(Exception e)
        {
            throw e;
        }   
        return false;
    }
    
    
     public boolean xoakhachhangcoma(int ma)
    {
        DataProvider pro = new DataProvider();
        int kq;
        try
        {
            pro.open();
            String query = "DELETE FROM KHACHHANG WHERE MaKH = "+ ma;
            kq = pro.excuteupdate(query);
            if(kq>=0)
                return true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }   
        return false;
    }
    
    
    public boolean xoakhachhang(KHACHHANGDTO dto)
    {
        DataProvider pro = new DataProvider();
        int kq;
        try
        {
            pro.open();
            String query = "DELETE FROM KhachHang WHERE MaKH = "+dto.getMakh();
            kq = pro.excuteupdate(query);
            if(kq>=0)
                return true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }   
        return false;
    }
    
    
    public boolean suakhachhang(KHACHHANGDTO dto)
    {
        DataProvider pro = new DataProvider();
        int kq;
        try
        {
            pro.open();
            String query = "UPDATE KhachHang set "
                    + "TenKH='"+dto.getTenkh()
                    +"',DiaChi='"+dto.getDiachi()
                    +"',Phone='"+dto.getPhone()+"' where MaKH= "+dto.getMakh();
            kq = pro.excuteupdate(query);
            if(kq>=0)
                return true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }   
        return false;
    }
}
